package com.bjmashibing.system.huawei.jdbc;

import com.bjmashibing.system.huawei.domain.Emp;
import com.bjmashibing.system.huawei.domain.Machine;
import com.bjmashibing.system.huawei.domain.Token;
import com.bjmashibing.system.huawei.static_var;
import com.bjmashibing.system.huawei.udp_cmd;
import com.bjmashibing.system.huawei.utils.JDBCUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;
import java.util.Map;


/**
 * @Classname SpringJdbcTemplate1
 * @Description TODO
 * @Date 2020/9/5/005 11:57
 * @Created by Administrator
 */
public class SpringJdbcTemplate {
    private final static Logger logger = LoggerFactory.getLogger(SpringJdbcTemplate.class);
    //1. 获取JDBCTemplate对象
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());


    public void machine_insert(String machine_name,int macine_type,String version,String card_type){
        try {
            Integer count_temp = machine_queryForOne(machine_name);
            if (count_temp > 0) {
                logger.info("Error,the machine:" + machine_name + " is exist......");
                return;
            }

            java.util.Date date = new java.util.Date();          // 获取一个Date对象
            Timestamp timeStamp = new Timestamp(date.getTime());
            String sql = "insert into machine(machine_name,machine_type,version,card_type,create_time) values(?,?,?,?,?)";
//        int count = template.update(sql,  "8612345673", 4,"6.43","物联卡",timeStamp);
//        int count = template.update(sql,  "8612345674", 4,"6.43","非物联卡",timeStamp);
//        int count = template.update(sql,  "8612345676", 4,"6.43","平台计费卡",timeStamp);
            int count = template.update(sql,  machine_name, macine_type,version,card_type,timeStamp);
            logger.info(String.valueOf(count));
        } catch (DataAccessException e) {
            logger.info("machine_insert,error=",e);
            e.printStackTrace();
        }

    }
/*    public void token_insert(String str_token,String str_language,String str_brand,String str_machine_name){
//        String str_token = "113AHCSfzBEvoUBuyVwIYEslxTpzSusNRzCMBtHJvx__pRxzTrx5y33JfGYzOm9EVpd-VShWppZ-uJCvQwemeWPP_mfTTgqwdkVaXgqonSsObs9rh1SoNsRajDkB3v2_fxhpA";
//        String str_token = "114AHCSfzBEvoUBuyVwIYEslxTpzSusNRzCMBtHJvx__pRxzTrx5y33JfGYzOm9EVpd-VShWppZ-uJCvQwemeWPP_mfTTgqwdkVaXgqonSsObs9rh1SoNsRajDkB3v2_fxhpA";
//        String str_token = "115AHCSfzBEvoUBuyVwIYEslxTpzSusNRzCMBtHJvx__pRxzTrx5y33JfGYzOm9EVpd-VShWppZ-uJCvQwemeWPP_mfTTgqwdkVaXgqonSsObs9rh1SoNsRajDkB3v2_fxhpA";
        Integer i_machine_id = 0;
        Integer count_temp2 = machine_queryForOne(str_machine_name);
        if (count_temp2 > 0) {
            i_machine_id = machine_queryForMap_id(str_machine_name);

        }else{
            logger.info("Error,the machine:" + str_machine_name + " is not exist......");
            return;
        }

        Integer count_temp = token_queryForOne(str_token);
        if (count_temp > 0) {
            logger.info("Error,the machine:" + str_token + " is exist......");
            return;
        }
        java.util.Date date = new java.util.Date();          // 获取一个Date对象
        Timestamp timeStamp = new Timestamp(date.getTime());
        String sql = "insert into token(token_name,language,brand,create_time,machine_id) values(?,?,?,?,?)";
//        int count = template.update(sql,  str_token,"China", "华为",timeStamp,1);
        int count = template.update(sql,  str_token,str_language, str_brand,timeStamp,i_machine_id);
        logger.info(String.valueOf(count));

    }*/
    public void token_insert_according_to_machine_id(String str_token,String str_language,String str_brand,String str_machine_name,Integer length_token,Integer receipt){
//        String str_token = "113AHCSfzBEvoUBuyVwIYEslxTpzSusNRzCMBtHJvx__pRxzTrx5y33JfGYzOm9EVpd-VShWppZ-uJCvQwemeWPP_mfTTgqwdkVaXgqonSsObs9rh1SoNsRajDkB3v2_fxhpA";
//        String str_token = "114AHCSfzBEvoUBuyVwIYEslxTpzSusNRzCMBtHJvx__pRxzTrx5y33JfGYzOm9EVpd-VShWppZ-uJCvQwemeWPP_mfTTgqwdkVaXgqonSsObs9rh1SoNsRajDkB3v2_fxhpA";
//        String str_token = "115AHCSfzBEvoUBuyVwIYEslxTpzSusNRzCMBtHJvx__pRxzTrx5y33JfGYzOm9EVpd-VShWppZ-uJCvQwemeWPP_mfTTgqwdkVaXgqonSsObs9rh1SoNsRajDkB3v2_fxhpA";
        try {
            Integer i_machine_id = 0;
            Integer count_temp2 = machine_queryForOne(str_machine_name);
            if (count_temp2 > 0) {
                i_machine_id = machine_queryForMap_id(str_machine_name);

            }else{
                logger.info("Error,the machine:" + str_machine_name + " is not exist......");
                return;
            }

//            Integer count_temp = token_queryForOne_according_to_machine_id(str_token,i_machine_id);
            Integer count_temp = token_queryForOne(str_token);
            if (count_temp > 0) {
                logger.info("Error,the machine:" + str_machine_name + " have the token," + str_token + " is exist......");
                return;
            }
            java.util.Date date = new java.util.Date();          // 获取一个Date对象
            Timestamp timeStamp = new Timestamp(date.getTime());
            String sql = "insert into token(token_name,language,brand,create_time,machine_id,length_token,receipt) values(?,?,?,?,?,?,?)";
//        int count = template.update(sql,  str_token,"China", "华为",timeStamp,1);
            int count = template.update(sql,  str_token,str_language, str_brand,timeStamp,i_machine_id,length_token,receipt);
            logger.info(String.valueOf(count));
        } catch (DataAccessException e) {
            logger.info("token_insert_according_to_machine_id,error=",e);
            e.printStackTrace();
        }

    }
    public void token_update_receipt(String str_token,Integer receipt){
        try {
            java.util.Date date = new java.util.Date();          // 获取一个Date对象
            Timestamp timeStamp = new Timestamp(date.getTime());
           /* String sql = "update token set receipt=" + receipt + ",create_time=" + timeStamp + "  where token_name = '" + str_token + "'";
            logger.info("sql=" + sql);
            int count = template.update(sql);
            logger.info(String.valueOf(count));*/
            String sql = "update token set create_time=?,receipt=? where token_name=?";
            int count = template.update(sql, new Object[]{timeStamp,receipt,str_token});
//            logger.info("token_update_receipt，success，count=" + count);
        } catch (DataAccessException e) {
            logger.info("token_update_receipt,error=",e);
            e.printStackTrace();
        }

    }
    public void token_delete(String str_token){
        try {
            String sql = "delete from token where token_name = '" + str_token + "'";
            int count = template.update(sql);
            logger.info(String.valueOf(count));
        } catch (DataAccessException e) {
            logger.info("token_delete,error=",e);
            e.printStackTrace();
        }

    }
/*    public void token_insert2(String str_token,String str_language,String str_brand,int macine_id){
//        String str_token = "113AHCSfzBEvoUBuyVwIYEslxTpzSusNRzCMBtHJvx__pRxzTrx5y33JfGYzOm9EVpd-VShWppZ-uJCvQwemeWPP_mfTTgqwdkVaXgqonSsObs9rh1SoNsRajDkB3v2_fxhpA";
//        String str_token = "114AHCSfzBEvoUBuyVwIYEslxTpzSusNRzCMBtHJvx__pRxzTrx5y33JfGYzOm9EVpd-VShWppZ-uJCvQwemeWPP_mfTTgqwdkVaXgqonSsObs9rh1SoNsRajDkB3v2_fxhpA";
//        String str_token = "115AHCSfzBEvoUBuyVwIYEslxTpzSusNRzCMBtHJvx__pRxzTrx5y33JfGYzOm9EVpd-VShWppZ-uJCvQwemeWPP_mfTTgqwdkVaXgqonSsObs9rh1SoNsRajDkB3v2_fxhpA";
        Integer i_machine_id = macine_id;


        Integer count_temp = token_queryForOne(str_token);
        if (count_temp > 0) {
            logger.info("Error,the machine:" + str_token + " is exist......");
            return;
        }
        java.util.Date date = new java.util.Date();          // 获取一个Date对象
        Timestamp timeStamp = new Timestamp(date.getTime());
        String sql = "insert into token(token_name,language,brand,create_time,machine_id) values(?,?,?,?,?)";
//        int count = template.update(sql,  str_token,"China", "华为",timeStamp,1);
        int count = template.update(sql,  str_token,str_language, str_brand,timeStamp,i_machine_id);
        logger.info(String.valueOf(count));

    }*/



    /**
     * 4.查询id为1001的记录，将其封装为Map集合
     * 注意：这个方法查询的结果集长度只能是1
     */

    public Integer machine_queryForOne(String machine_name) {
        try {
            String sql = "select count(*) from machine where machine_name = '" + machine_name +"'";
            //        调用方法获得记录数
            int count = template.queryForObject(sql, Integer.class);
//            logger.info("machine_queryForOne,machine数据总数：" + count);
            return count;
        } catch (DataAccessException e) {
            logger.info("machine_queryForOne,error=",e);
            e.printStackTrace();
        }
        return -1;
    }
    public Integer token_queryForOne(String token_name) {
        String sql = "select count(*) from token where token_name = '" + token_name +"'";
        //        调用方法获得记录数
        int count = template.queryForObject(sql, Integer.class);
//        logger.info("token_queryForOne,token数据总数：" + count);
        return count;
    }
    public Integer token_queryForOne_according_to_machine_id(String token_name,Integer machine_id) {
        try {
            String sql = "select count(*) from token where token_name = '" + token_name +"' and machine_id=" + machine_id;
            //        调用方法获得记录数
            int count = template.queryForObject(sql, Integer.class);
//            logger.info("token_queryForOne_according_to_machine_id,token数据总数：" + count);
            return count;
        } catch (DataAccessException e) {
            logger.info("token_queryForOne_according_to_machine_id,error=",e);
            e.printStackTrace();
        }
        return -1;
    }

   /* public void machine_queryForMap(String machine_name){
        String sql = "select * from machine where machine_name = ?";
        Map<String, Object> map = template.queryForMap(sql, machine_name);
        logger.info("map=" + map);
        logger.info("machine_name" + map.get("machine_name"));

    }*/
    public Integer machine_queryForMap_id(String machine_name){
        try {
            String sql = "select * from machine where machine_name = ?";
            Map<String, Object> map = template.queryForMap(sql, machine_name);
//            logger.info("map=" + map);
//            logger.info("machine_name=" + map.get("machine_name"));
//            logger.info("machine,id=" + map.get("id"));
            return (Integer) map.get("id");
        } catch (DataAccessException e) {
            logger.info("machine_queryForMap_id,error=",e);
            e.printStackTrace();
        }
        return -1;
    }
    public void token_queryForMap_schedule(){
//        String sql = "SELECT TIMESTAMPDIFF(DAY, \"2021-02-25 00:00:00\",now()) as time_diff";
        /*for (int i = 0; i < list.size(); i++) {
            logger.info("time_diff=" + list.get(i).get("time_diff"));
            Integer days = Integer.valueOf(list.get(i).get("time_diff").toString());
            logger.info("days=" + days);
        }*/
        try {
            String sql = "select token_name,machine_id from token where create_time<curdate()-interval 15 day;";
            List<Map<String, Object>> list = template.queryForList(sql);
//        Map<String, Object> map = template.queryForMap(sql);
//            logger.info("list=" + list);
//            logger.info("token_queryForMap_schedule,static_var.map_token=" + static_var.map_token.toString());

            for (int i = 0; i < list.size(); i++) {
//                logger.info("token_name=" + list.get(i).get("token_name"));
//                logger.info("machine_id=" + list.get(i).get("machine_id"));
                String sql2 = "select machine_name from machine where id=?";
                List<Map<String, Object>> list2 = template.queryForList(sql2,Integer.valueOf(list.get(i).get("machine_id").toString()));
//                logger.info("list2=" + list2);
//                logger.info("machine_name=" + list2.get(0).get("machine_name"));
                token_delete(list.get(i).get("token_name").toString());
                static_var.remove_token_according_to_machine_name(list2.get(0).get("machine_name").toString(),list.get(i).get("token_name").toString());
            }

//            logger.info("token_queryForMap_schedule,static_var.map_token=" + static_var.map_token.toString());
        } catch (DataAccessException e) {
            logger.info(e.toString());
            e.printStackTrace();
        } catch (NumberFormatException e) {
            logger.info("token_queryForMap_schedule,error=",e);
            e.printStackTrace();
        }


    }
    public void token_queryForMap_huawei_receipt(String token){
        try {
            String sql = "select token_name,machine_id,length_token from token where token_name='" + token + "'";
            logger.info("sql=" + sql);
            List<Map<String, Object>> list_token = template.queryForList(sql);
//        Map<String, Object> map = template.queryForMap(sql);
//            logger.info("list_token=" + list_token);
//            logger.info("token_queryForMap_schedule,static_var.map_token=" + static_var.map_token.toString());

            for (int i = 0; i < list_token.size(); i++) {
//                logger.info("token_name=" + list_token.get(i).get("token_name"));
//                logger.info("machine_id=" + list_token.get(i).get("machine_id"));
//                logger.info("length_token=" + list_token.get(i).get("length_token"));
                Integer int_length_token = Integer.valueOf(list_token.get(i).get("length_token").toString());


                String sql2 = "select machine_name from machine where id=?";
                Map<String, Object> map_machine = template.queryForMap(sql2,Integer.valueOf(list_token.get(i).get("machine_id").toString()));
//                logger.info("map_machine=" + map_machine);
//                logger.info("machine_name=" + map_machine.get("machine_name"));

                token_delete(token);
                static_var.remove_token_according_to_machine_name(map_machine.get("machine_name").toString(),token);
            }

//            logger.info("token_queryForMap_schedule,static_var.map_token=" + static_var.map_token.toString());
        } catch (DataAccessException e) {
            logger.info(e.toString());
            e.printStackTrace();
        }catch (Exception e) {
            logger.info("token_queryForMap_huawei_receipt,error=",e);
            e.printStackTrace();
        }


    }
/*    public void token_queryForMap(String token_name){
        Integer count_temp = token_queryForOne(token_name);
        if (count_temp <= 0) {
            logger.info("Error,the token:" + token_name + " is exist......");
            return;
        }
        String sql = "select * from token where token_name = ?";
        Map<String, Object> map = template.queryForMap(sql, token_name);
        logger.info("map=" + map);
        logger.info("token_name" + map.get("token_name"));


    }*/

    /*public void machine_query_domain(String machine_name){
        String sql = "select * from machine where machine_name = '" + machine_name +"'";
        Machine machine = template.queryForObject(sql, new BeanPropertyRowMapper<Machine>(Machine.class));

        logger.info("machine=" + machine);
        logger.info("machine_name" + machine.getMachine_name());

    }*/
   /* public void token_query_domain(String token_name){
        Integer count_temp = token_queryForOne(token_name);
        if (count_temp <= 0) {
            logger.info("Error,the token:" + token_name + " is exist......");
            return;
        }
        String sql = "select * from token where token_name = '" + token_name +"'";
        Token token = template.queryForObject(sql, new BeanPropertyRowMapper<Token>(Token.class));
        logger.info("token=" + token);
        logger.info("token_name" + token.getToken_name());


    }*/
    /*public void machine_list_query(){
        String sql = "select * from machine";
        List<Machine> list = template.query(sql, new BeanPropertyRowMapper<Machine>(Machine.class));

        for (int i = 0;i < list.size();i++) {
            logger.info("machine_name" + list.get(i).getMachine_name());
        }
        for (Machine machine: list) {
            logger.info("machine=" + machine);
        }
    }*/
    public List<Machine> machine_list_query2(){
        try {
            String sql = "select * from machine";
            List<Machine> list = template.query(sql, new BeanPropertyRowMapper<Machine>(Machine.class));

        /*for (int i = 0;i < list.size();i++) {
            logger.info("machine_name" + list.get(i).getMachine_name());
        }
        for (Machine machine: list) {
            logger.info("machine=" + machine);
        }*/
            return list;
        } catch (DataAccessException e) {
            logger.info("machine_list_query2,error=",e);
            e.printStackTrace();
        }
        return null;
    }
    /*public void token_list_query(){
        String sql = "select * from token";
        List<Token> list = template.query(sql, new BeanPropertyRowMapper<Token>(Token.class));

        for (int i = 0;i < list.size();i++) {
            logger.info("token_name=" + list.get(i).getToken_name());
        }
        for (Token Token: list) {
            logger.info("Token=" + Token);
        }
    }*/
    public List<Token> token_list_query_for_machine_id(Integer machine_id){
//        String sql = "select * from token where machine_id = '" + machine_id + "'";
        try {
            String sql = "select * from token where machine_id = " + machine_id;
            List<Token> list = template.query(sql, new BeanPropertyRowMapper<Token>(Token.class));

        /*for (int i = 0;i < list.size();i++) {
            logger.info("token_name=" + list.get(i).getToken_name());
        }
        for (Token Token: list) {
            logger.info("Token=" + Token);
        }*/
            return list;
        } catch (DataAccessException e) {
            logger.info("token_list_query_for_machine_id,error=",e);
            e.printStackTrace();
        }
        return null;
    }


/////////////////////////////////////////////////////////////////////////////////////////////////////
    /*public void update(){
        //1.导入jar包

        //3.调用方法
        String sql = "update account set balance = 6000 where id = ?";
        int count = template.update(sql, 3);

        logger.info("count=" + count);
    }*/

    /**
     * 1. 修改1号数据的 salary 为 10000
     */

    /*public void update2(){

        //2. 定义sql
        String sql = "update emp set salary = 20000 where id = 1001";
        //3. 执行sql
        int count = template.update(sql);
        logger.info("count=" + count);
    }*/

    /**
     * 2. 添加一条记录
     */

    /*public void insert(){
        String sql = "insert into emp(id,ename,dept_id) values(?,?,?)";
        int count = template.update(sql, 1018, "郭靖2", 10);
        logger.info("count=" + count);

    }*/
    /**
     * 3.删除刚才添加的记录
     */

   /* public void delete(){
        String sql = "delete from emp where id = ?";
        int count = template.update(sql, 1015);
        logger.info("count=" + count);
    }
    public void queryForMap(){
        String sql = "select * from emp where id = ?";
        Map<String, Object> map = template.queryForMap(sql, 1001);
        logger.info("map=" + map);
        //{id=1001, ename=孙悟空, job_id=4, mgr=1004, joindate=2000-12-17, salary=10000.00, bonus=null, dept_id=20}

    }
    public void queryForMap2(){
        String sql = "select * from emp where id = ? or id = ?";
        Map<String, Object> map = template.queryForMap(sql, 1001,1002);
        logger.info("map=" + map);
        //{id=1001, ename=孙悟空, job_id=4, mgr=1004, joindate=2000-12-17, salary=10000.00, bonus=null, dept_id=20}

    }*/


    /**
     * 5. 查询所有记录，将其封装为List
     */

   /* public void queryForList(){
        String sql = "select * from emp";
        List<Map<String, Object>> list = template.queryForList(sql);

        for (Map<String, Object> stringObjectMap : list) {
            logger.info("stringObjectMap=" + stringObjectMap);
        }
    }*/

    /**
     * 6. 查询所有记录，将其封装为Emp对象的List集合
     */


    /*public void query(){
        String sql = "select * from emp";
        List<Emp> list = template.query(sql, new RowMapper<Emp>() {

            @Override
            public Emp mapRow(ResultSet rs, int i) throws SQLException {
                Emp emp = new Emp();
                int id = rs.getInt("id");
                String ename = rs.getString("ename");
                int job_id = rs.getInt("job_id");
                int mgr = rs.getInt("mgr");
                Date joindate = rs.getDate("joindate");
                double salary = rs.getDouble("salary");
                double bonus = rs.getDouble("bonus");
                int dept_id = rs.getInt("dept_id");

                emp.setId(id);
                emp.setEname(ename);
                emp.setJob_id(job_id);
                emp.setMgr(mgr);
                emp.setJoindate(joindate);
                emp.setSalary(salary);
                emp.setBonus(bonus);
                emp.setDept_id(dept_id);

                return emp;
            }
        });


        for (Emp emp : list) {
            logger.info("emp=" + emp);
        }
    }*/

    /**
     * 6. 查询所有记录，将其封装为Emp对象的List集合
     */






    /*public void query2(){
        String sql = "select * from emp";
        List<Emp> list = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));

        for (Emp emp : list) {
            logger.info("emp=" + emp);
        }
    }*/


    /**
     * 7. 查询总记录数
     */


    /*public void queryForObject(){
        String sql = "select count(id) from emp";
        Long total = template.queryForObject(sql, Long.class);
        logger.info("total=" + total);
    }*/
}
